package com.yjd.bi.etl.funds.service;

import com.yjd.bi.comm.utils.DbNameUtils;
import com.yjd.bi.etl.comm.BaseService;
import com.yjd.bi.etl.sql.FundsMonthSqlConstants;
import com.yjd.bi.report.model.BiDatabaseInfoModel;
import com.yjd.bi.report.util.BiUtil;
import com.yjd.comm.util.DateUtil;
import com.yjd.comm.util.FrameUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by yjd-wap on 2017/4/17.
 */
@Component("fundsUserBalanceService")
public class FundsUserBalanceService extends BaseService {

    public static final String querySql_1 = "select a.user_id, ";
    public static final String querySql_2 = " a.balance  from yjd_funds_data a where a.balance > 0 and a.id = (select max(t.id) from yjd_funds_data t where t.user_id = ";
    public static final String querySql_3 = " and t.uptime <= ";
    public static final String querySql_4 = " group by t.user_id ) ";
    public static final String querySql_5 = " a.balance  from yjd_bank_depo_funds_data a where a.balance > 0 and a.id = (select max(t.id) from yjd_bank_depo_funds_data t where t.user_id = ";
    public static final String querySql_6 = "SELECT * FROM yjd_user WHERE user_id in ";

    /**
     * 统计某一日期下 某些用户的余额
     *
     * @param queryList
     * @param statiisticsDate
     * @return
     * @throws Exception
     */
    public List queryUserBalance(List<Long> queryList, Long statiisticsDate) throws Exception {
        BiDatabaseInfoModel model = this.getBiDatabaseInfoModel();
        JdbcTemplate jt = BiUtil.getJdbcTemplate(model);
        List list = new ArrayList();
        int queryDate = Integer.parseInt(DateUtil.timestampToDateString(statiisticsDate, DateUtil.PATTERN_yyyyMMdd));
        Long currtime = FrameUtil.getTime(null);
        String preQueryFiled = querySql_1 + queryDate + " as funds_date , " + currtime + " as  create_time , ";
        String queryAll = "";
        int i = 0;
        List<Map<String, Object>> userList = jt.queryForList(querySql_6 + " (" + FrameUtil.arrayToSql(queryList) + ") ");
        Map<Long, Integer> userMap = FrameUtil.newHashMap();
        for (Map user : userList) {
            userMap.put(Long.parseLong(user.get("user_id").toString()), Integer.valueOf(user.get("bank_depo").toString()));
        }
        for (Long user_id : queryList) {

            //判断是否是存管用户，是->查询yjd_bank_depo_funds_data表，否->查询yjd_funds_data表
            Integer bank_depo = userMap.get(user_id);
            String queryFiled = preQueryFiled + (bank_depo.intValue() == 1 ? querySql_5 : querySql_2);

            if (i == 0) {
                queryAll = queryFiled + user_id + querySql_3 + statiisticsDate + querySql_4;
            } else {
                queryAll += " union " + queryFiled + user_id + querySql_3 + statiisticsDate + querySql_4;
            }
            i++;
        }
        list = jt.queryForList(queryAll);
        return list;

    }


    /**
     * 查询最大运行时间
     *
     * @return
     * @throws Exception
     */
    public Map getMaxInfo() throws Exception {
        BiDatabaseInfoModel model = this.getBiDatabaseInfoModel();
        JdbcTemplate jt = BiUtil.getJdbcTemplate(model);
        List res = jt.queryForList(FundsMonthSqlConstants.run_max_info);

        Map<String, Object> map = new HashMap();
        if (res != null && res.size() > 0) {
            map.put("run_month", String.valueOf(((Map) res.get(0)).get("run_month")));
            map.put("run_date", String.valueOf(((Map) res.get(0)).get("run_date")));
            map.put("start_userid", String.valueOf(((Map) res.get(0)).get("start_userid")));
            map.put("date_status", String.valueOf(((Map) res.get(0)).get("date_status")));
        }
        return map;
    }


    /**
     * 根据运行日期查询当前日期最大注册用户id
     *
     * @param currDate
     * @return
     * @throws Exception
     */
    public Long getMaxUserIdByRegDate(Long currDate) throws Exception {
        Long max_user_id = null;
        BiDatabaseInfoModel model = this.getBiDatabaseInfoModel();
        JdbcTemplate jt = BiUtil.getJdbcTemplate(model);
        List res = jt.queryForList(FundsMonthSqlConstants.get_max_user_id, new Object[]{currDate});
        if (res != null && res.size() > 0) {
            max_user_id = Long.parseLong(String.valueOf(((Map) res.get(0)).get("user_id")));
        }
        return max_user_id;
    }


    /**
     * 根据用户开始编号和结束编号进行每500条分段
     *
     * @param start_userid
     * @param max_userid
     * @return
     * @throws Exception
     */
    public Map<String, Object> getHandleUserList(Long start_userid, Long max_userid) throws Exception {
        Map<String, Object> handMap = new HashMap<String, Object>();
        BiDatabaseInfoModel model = this.getBiDatabaseInfoModel();
        JdbcTemplate jt = BiUtil.getJdbcTemplate(model);
        String sql_1 = " select a.user_id from yjd_user a where a.user_id > ";
        String sql_2 = " and a.user_id <=  ";
        String sql_3 = " and a.user_id%30=  ";
       // String sql_4 = " and exists (select 1 from yjd_funds_data b where a.user_id = b.user_id) ";


        sql_1 = sql_1 + start_userid;
        sql_2 = sql_2 + max_userid;


        Long lastUserId = null;
        for (int i = 0; i < 30; i++) {
            String slq5 = sql_3 + i;
            String querySql = sql_1 + sql_2 + slq5 ;
            List userList = jt.queryForList(querySql);
            if (userList != null && userList.size() > 0) {
                handMap.put("list" + i, convertUserId(userList));
                lastUserId = Long.parseLong(((Map) userList.get(userList.size() - 1)).get("user_id").toString());
            }
        }

        if (lastUserId != null) {
            handMap.put("lastUserId", lastUserId);
        }

        return handMap;
    }

    private List<Long> convertUserId(List list) throws Exception {
        List<Long> userList = new ArrayList<>();
        if (list != null && list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                userList.add(Long.parseLong(((Map) list.get(i)).get("user_id").toString()));
            }
        }
        return userList;
    }

    /**
     * 插入资金运行记录
     *
     * @param month
     * @param date
     * @param max_user
     * @param currtime
     * @throws Exception
     */
    public void insertyfundsRunRecord(int month, int date, Long max_user, long start_userid, Long currtime) throws Exception {
        String insertSql = "insert into yjd_funds_run_record (run_month,max_user_id,start_userid,run_date,date_status,create_time,update_time) values (?,?,?,?,?,?,?)";
        String del_sql = "delete from yjd_funds_run_record where run_month=? and  run_date =?";
        BiDatabaseInfoModel model = this.getBiDatabaseInfoModel();
        JdbcTemplate jt = BiUtil.getJdbcTemplate(model);
        jt.update(del_sql, new Object[]{month, date});
        jt.update(insertSql, new Object[]{month, max_user, start_userid, date, 0, currtime, currtime});
    }

    /**
     * 修改资金运行记录信息
     *
     * @param month
     * @param date
     * @param start_user
     * @param currtime
     * @param date_status
     * @throws Exception
     */
    public void updatefundsRunRecord(int month, int date, Long start_user, Long currtime, Integer date_status) throws Exception {
        String updateSql = "update yjd_funds_run_record  set start_userid=?,update_time=?,date_status=? where run_month=? and run_date=?";
        BiDatabaseInfoModel model = this.getBiDatabaseInfoModel();
        JdbcTemplate jt = BiUtil.getJdbcTemplate(model);
        jt.update(updateSql, new Object[]{start_user, currtime, date_status, month, date});

    }

    /**
     * 批量插入用户按天生成的数据
     *
     * @param month
     * @param currDate
     * @param maxUserid
     * @param balanceList
     * @param overFlag
     * @throws Exception
     */
    public void insertBatchUserBalanceByMonth(int month, int currDate, Long maxUserid, List balanceList, boolean overFlag) throws Exception {
        BiDatabaseInfoModel model = this.getBiDatabaseInfoModel();
        JdbcTemplate jt = BiUtil.getJdbcTemplate(model);
        String insertSql = "insert into yjd_balance_month_" + month + " (user_id,funds_date,create_time,balance) values (?,?,?,?)";
        if (balanceList.size() > 1000) {
            int numbs = balanceList.size() / 1000;
            for (int i = 0; i < numbs; i++) {
                List tempList = balanceList.subList(i * 1000, (i + 1) * 1000);
                jt.batchUpdate(insertSql, convertBalanceList(tempList));
            }
            jt.batchUpdate(insertSql, convertBalanceList(balanceList.subList(numbs * 1000, balanceList.size())));
        } else {
            jt.batchUpdate(insertSql, convertBalanceList(balanceList));
        }
        Integer date_status = 0;
        if (overFlag) {
            date_status = 1;
        }
        updatefundsRunRecord(month, currDate, maxUserid, FrameUtil.getTime(null), date_status);
    }


    /**
     * 插入医借贷平台余额记录表
     *
     * @param month
     * @param currDate
     * @throws Exception
     */
    public void insertYjdPfBalance(int month, int currDate) throws Exception {
        //投资人余额
        String yjd_user_balance_invest = "select  ifnull(sum(a.balance),0) as balance from yjd_balance_month_" + month + "  a , yjd_user b where a.user_id = b.user_id and a.user_id not in (465766,499957,653204) " +
                "and  b.user_type & 4 != 4 and a.funds_date = ? ";
        //借款人余额
        String yjd_user_balance_borrow = "select  ifnull(sum(a.balance),0) as balance from yjd_balance_month_" + month + "  a , yjd_user b where a.user_id = b.user_id and a.user_id not in (465766,499957,653204) " +
                "and  b.user_type & 4 = 4 and a.funds_date = ? ";
        //投资人余额 银行存管
        String yjd_bank_depo_balance_invest = "SELECT IFNULL(sum(a.balance), 0.00) AS available FROM " + DbNameUtils.get("yjd_bank_depo_funds_data") + " a, ( SELECT max(t.id) AS id FROM " + DbNameUtils.get("yjd_bank_depo_funds_data") +
                " t WHERE t.uptime <= unix_timestamp(?) GROUP BY t.user_id ) b, " + DbNameUtils.get("yjd_user") + " u WHERE a.user_id = u.user_id and u.user_type &4!=4 and a.id = b.id";

        //借款人余额  银行存管
        String yjd_bank_depo_balance_borrow =  "SELECT IFNULL(sum(a.balance), 0.00) AS available FROM " + DbNameUtils.get("yjd_bank_depo_funds_data") + " a, ( SELECT max(t.id) AS id FROM " + DbNameUtils.get("yjd_bank_depo_funds_data") +
                " t WHERE t.uptime <= unix_timestamp(?) GROUP BY t.user_id ) b, " + DbNameUtils.get("yjd_user") + " u WHERE a.user_id = u.user_id and u.user_type &4=4 and a.id = b.id";

        BiDatabaseInfoModel model = this.getBiDatabaseInfoModel();
        JdbcTemplate jt = BiUtil.getJdbcTemplate(model);
        List inesetRes = jt.queryForList(yjd_user_balance_invest, new Object[]{currDate});
        List borrowRes = jt.queryForList(yjd_user_balance_borrow, new Object[]{currDate});
        String countDate = currDate+"235959";
        List bankDepoInvest = jt.queryForList(yjd_bank_depo_balance_invest, new Object[]{countDate});
        List bankDepoBorrow = jt.queryForList(yjd_bank_depo_balance_borrow, new Object[]{countDate});

        Double investBalance = 0d;
        Double borrowBalance = 0d;
        Double bankDepoInvestBalance = 0d;
        Double bankDepoBorrowBalance = 0d;



        if (inesetRes != null && inesetRes.size() > 0) {
            investBalance = Double.parseDouble(String.valueOf(((Map) inesetRes.get(0)).get("balance")));
        }

        if (borrowRes != null && borrowRes.size() > 0) {
            borrowBalance = Double.parseDouble(String.valueOf(((Map) borrowRes.get(0)).get("balance")));
        }

        if (bankDepoInvest != null && bankDepoInvest.size() > 0) {
            bankDepoInvestBalance = Double.parseDouble(String.valueOf(((Map) bankDepoInvest.get(0)).get("available")));
        }

        if (bankDepoBorrow != null && bankDepoBorrow.size() > 0) {
            bankDepoBorrowBalance = Double.parseDouble(String.valueOf(((Map) bankDepoBorrow.get(0)).get("available")));
        }
        jt.update(FundsMonthSqlConstants.yjd_pf_balance_insert, new Object[]{investBalance, borrowBalance,bankDepoInvestBalance,bankDepoBorrowBalance,currDate, FrameUtil.getTime(null)});
    }


    /**
     * 删除大于当前跑的user_id记录
     *
     * @param month
     * @param currDate
     * @param start_userid
     * @throws Exception
     */
    public void delBalanceMonth(int month, int currDate, Long start_userid) throws Exception {
        String del_sql = "delete from  yjd_balance_month_" + month + "  where funds_date = ? and user_id >= ? ";
        BiDatabaseInfoModel model = this.getBiDatabaseInfoModel();
        JdbcTemplate jt = BiUtil.getJdbcTemplate(model);
        jt.update(del_sql, new Object[]{currDate, start_userid});
    }


    public List<Object[]> convertBalanceList(List balanceList) throws Exception {
        List<Object[]> insertList = new ArrayList<Object[]>();

        if (balanceList != null && balanceList.size() > 0) {
            for (int i = 0; i < balanceList.size(); i++) {
                Long user_id = 0L;
                int funds_date = 0;
                Long create_time = 0L;
                Double balance = 0d;

                Map<String, Object> obj = (Map<String, Object>) balanceList.get(i);
                if (obj.get("user_id") != null) {
                    user_id = Long.parseLong(obj.get("user_id").toString());
                }

                if (obj.get("funds_date") != null) {
                    funds_date = Integer.parseInt(obj.get("funds_date").toString());
                }

                if (obj.get("create_time") != null) {
                    create_time = Long.parseLong(obj.get("create_time").toString());
                }

                if (obj.get("balance") != null) {
                    balance = Double.parseDouble(obj.get("balance").toString());
                }
                insertList.add(new Object[]{user_id, funds_date, create_time, balance});

            }
        }

        return insertList;
    }

}
